/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sistemperpustakaan.model;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import sistemperpustakaan.entity.HariLibur;
import sistemperpustakaan.utility.DbUtility;

/**
 *
 * @author EKo
 */
public class HariLiburModel {

    Connection connection;
    List<HariLibur> listHariLibur;
    HariLibur hariLibur;

    public HariLiburModel() {
        connection = new DbUtility().getConnection();
    }

    public List<HariLibur> getListHariLibur(String tanggala, String tanggalb, String keterangan) {
        listHariLibur = new ArrayList<HariLibur>();
        String query = "CALL pCariHariLibur ('"+tanggala+"','"+tanggalb+"','"+ keterangan + "')";
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(query);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                hariLibur = new HariLibur(
                        rs.getDate(1), rs.getString(2));
                listHariLibur.add(hariLibur);

            }

        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data" + ex.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return listHariLibur;
    }
    public HariLibur getHariLibur(String tanggal) {
        String query = "SELECT * FROM hari_libur WHERE tanggal='" + tanggal+"'";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(query);//statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {

                hariLibur = new HariLibur(rs.getDate(1), rs.getString(2));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data \n Error: " + e.getMessage());
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return hariLibur;
    }
    public boolean insertHariLibur(String tanggal, String keterangan) {
        String query = "INSERT INTO hari_libur VALUES('" + tanggal + "','" + keterangan + "')";

        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return true;
    }
    public boolean updateHariLibur(String tanggalLama, String tanggal, String keterangan) {
        String query = "UPDATE hari_libur SET  tanggal='" +tanggal+ "', keterangan='" + keterangan + "' WHERE tanggal='" + tanggalLama + "'";
        //String queryDenda = "INSERT INTO Denda (transaksi_id,transaksidetail_id,tanggal,keterangan_id,bayar) VALUES (?,?,"+tanggalKembali+",1,"+bayar+")";
        PreparedStatement statement = null;
//        System.out.println(tanggalKembali);
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when Update data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                }
            }
        }
        return true;
    }
    public boolean deleteHariLibur(String tanggal) {
        String query = "DELETE FROM hari_libur WHERE tanggal='"+tanggal+"'";
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            connection.setAutoCommit(false);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when delete data \n Error: " + e.getMessage());
            try {
                connection.rollback();
                statement.close();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }
}
